﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data;
using System.Data.Common;

namespace SQLiteHelp
{
    public class NewsCategory
    {
        public int ID { get; set; }
        public string CategoryName { get; set; }


        static Database db = DatabaseFactory.CreateDatabase("SQLiteDB");

        public static DataTable GetCategoryList()
        {
            string getContent = "Select * from news_category order by category_id desc";
            DbCommand cmd = db.GetSqlStringCommand(getContent);
            return db.ExecuteDataSet(cmd).Tables[0];
        }

        public static string GetCategoryNameByID(int category_id)
        {
            string getContent = "Select category_name from news_category where category_id=" + category_id;
            string ret = "";
            DbCommand cmd = db.GetSqlStringCommand(getContent);
            IDataReader reader = db.ExecuteReader(cmd);
            if (reader.Read())
            {
                ret = reader[0].ToString();
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return ret;
        }

        public static int CreateNewsCategory(string category_name)
        {
            string strInsert = "insert into news_category(category_name) values (@category_name)";
            DbCommand cmd = db.GetSqlStringCommand(strInsert);
            db.AddInParameter(cmd, "@category_name", DbType.String, category_name);
            return db.ExecuteNonQuery(cmd);
        }

        public static int UpdateCategory(int category_id,string category_name)
        {
            string strInsert = "update news_category set category_name=@category_name where category_id=@category_id";
            DbCommand cmd = db.GetSqlStringCommand(strInsert);
            db.AddInParameter(cmd, "@category_name", DbType.String, category_name);
            db.AddInParameter(cmd, "@category_id", DbType.Int32, category_id);
            return db.ExecuteNonQuery(cmd);
        }

        public static int DeleteCategory(int category_id)
        {
            string strInsert = "delete from news_category where  category_id=@category_id";
            DbCommand cmd = db.GetSqlStringCommand(strInsert);
            db.AddInParameter(cmd, "@category_id", DbType.Int32, category_id);
            return db.ExecuteNonQuery(cmd);
        }
    }
}
